package database;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import sun.misc.Version;

import domain.Speler;
import domain.SpelerTekenChar;
import domain.exceptions.KomtInOrdeException;

public class DatabaseOnline implements iDatabase {
	private ArrayList<Speler> spelers = null;
	Properties propertiesDatabase = null;

	@Override
	public ArrayList<Speler> getSpelers() {
		return this.spelers;
	}

	@Override
	public void loadDatabase() throws IOException, KomtInOrdeException {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement pst = null;

		try {
			con = createConnection();
			pst = con.prepareStatement("SELECT "
					+ propertiesDatabase.getProperty("kolom1") + ", "
					+ propertiesDatabase.getProperty("kolom2") + " FROM "
					+ propertiesDatabase.getProperty("table") + ";");
			rs = pst.executeQuery();

			verwerkRecordsetNaarSpelers(rs);

		} catch (SQLException ex) {
			handleSQLExceptionSevere(ex);

		} finally {
			try {
				closeConnection(con, st, rs);

			} catch (SQLException ex) {
				handleSQLExceptionWarning(ex);
			}
		}
	}

	/**
	 * @param ex
	 */
	private void handleSQLExceptionWarning(SQLException ex) {
		Logger lgr = Logger.getLogger(Version.class.getName());
		lgr.log(Level.WARNING, ex.getMessage(), ex);
	}

	/**
	 * @param ex
	 */
	private void handleSQLExceptionSevere(SQLException ex) {
		Logger lgr = Logger.getLogger(Version.class.getName());
		lgr.log(Level.SEVERE, ex.getMessage(), ex);
	}

	/**
	 * @param url
	 * @param user
	 * @param password
	 * @return
	 * @throws SQLException
	 */
	private Connection createConnection() throws SQLException {
		String url = propertiesDatabase.getProperty("url"); // a la
															// "jdbc:postgresql://url:poort"
		String user = propertiesDatabase.getProperty("user");
		String password = propertiesDatabase.getProperty("pass");
		Connection con;
		con = DriverManager.getConnection(url, user, password);
		return con;
	}

	/**
	 * @param con
	 * @param st
	 * @param rs
	 * @throws SQLException
	 */
	private void closeConnection(Connection con, Statement st, ResultSet rs)
			throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (st != null) {
			st.close();
		}
		if (con != null) {
			con.close();
		}
	}

	/**
	 * @param rs
	 * @throws SQLException
	 */
	private void verwerkRecordsetNaarSpelers(ResultSet rs) throws SQLException {
		while (rs.next()) {
			this.addSpeler(rs.getString(1), rs.getInt(2)); // kolom 1 =
																// naam,
																// kolom 2 =
																// score
		}
	}

	@Override
	public void schrijfGegevensWeg() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement pst = null;

		try {
			con = createConnection();
			boolean check = false;
			ArrayList<String> updateList = new ArrayList<String>();
			ArrayList<String> addList = new ArrayList<String>();

			String query = createSelectQueryExistingPlayers();

			pst = con.prepareStatement(query);
			boolean isResult = pst.execute();
			do {
				rs = pst.getResultSet();

				while (rs.next()) {
					check = false;
					for (int y = 0; y < spelers.size() && !check; y++) {
						if (spelers.get(y).equals(rs.getString(1)))
							check = true;
					}

					if (check)
						updateList.add(rs.getString(1));
					else if (!check)
						addList.add(rs.getString(1));
				}
				isResult = pst.getMoreResults();
				
				
				Speler speler = null;
				query = "";
				for (int i=0; i<=addList.size(); i++) {
					check = false;
					for (int y = 0; y<=spelers.size()&&!check;y++){
						if(spelers.get(y).getSpelerNaam().equals(addList.get(i).toString())){
							speler = spelers.get(y);
							check = true;
							query = "INSERT INTO "+ propertiesDatabase.getProperty("table") +" VALUES("+ speler.getSpelerNaam() +", "+ speler.getScore() +")";
							st.executeUpdate(query);
						}
					}
				}

				// Functie die de update en add list gebruikt voor de data in de -- insert done
				// DB te kunnen voeren

			} while (isResult);
		} catch (SQLException ex) {
			handleSQLExceptionSevere(ex);

		} finally {
			try {
				closeConnection(con, st, rs);

			} catch (SQLException ex) {
				handleSQLExceptionWarning(ex);
			}
		}
	}

	/**
	 * @param query
	 * @return
	 */
	private String createSelectQueryExistingPlayers() {
		String query = "";
		for (int i = 0; i < this.getSpelers().size(); i++) {
			query += "SELECT " + propertiesDatabase.getProperty("kolom1")
					+ " FROM " + propertiesDatabase.getProperty("table")
					+ " WHERE " + propertiesDatabase.getProperty("kolom1")
					+ "=" + "\"" + spelers.get(i).getSpelerNaam() + "\"" + ";";
		}
		return query;
	}

	@Override
	public Speler getSpeler(String spelerNaam) {
		Speler sp = new Speler(spelerNaam, new SpelerTekenChar('O'), 0);
		Speler out = null;
		for (Speler s : spelers) {
			if (s.equals(sp))
				out = s;
		}
		return out;
	}

	@Override
	public boolean addSpeler(String spelerNaam, int score) {
		boolean output = spelers.add(new Speler(spelerNaam, score));
		return output;
	}

	@Override
	public void loadProperties() throws FileNotFoundException, IOException {
		propertiesDatabase = new Properties();
		FileInputStream in = new FileInputStream(
				"./src/database/propertiesDatabase.prp");
		propertiesDatabase.load(in);
		in.close();
	}

	@Override
	public void updateSpelerScore(Speler speler) {
		for (Speler s : spelers) {
			if (s.getSpelerNaam().equals(speler.getSpelerNaam())){
				s = speler;
			}
		}
	}

}
